package com.xingxuecn.limit.dao.impl;

import com.xingxuecn.limit.common.model.Role;
import com.xingxuecn.limit.common.model.vo.Page;
import com.xingxuecn.limit.common.utils.DbUtils;
import com.xingxuecn.limit.dao.RoleDao;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.LinkedList;
import java.util.List;

/**
 * @author Zy at 2018/9/11 0011 上午 9:34
 */
public class RoleDaoImpl implements RoleDao {
        private QueryRunner qr = new QueryRunner();

        @Override
        public List<Role> findAll() throws SQLException {
            String sql = "select * from role";
            Connection connection = DbUtils.getConnection();
            List<Role> result;
            try {
                result = qr.query(connection, sql, new BeanListHandler<Role>(Role.class));
            } finally {
                connection.close();
            }
            return result;
        }

        @Override
        public List<Role> search(Role role, Integer... limit) throws SQLException {
            StringBuffer sql = new StringBuffer("select * from role where 1=1");
            List<Object> jdbcParams = new LinkedList<>();
            if (role.getId() != null) {
                sql.append(" and id = ?");
                jdbcParams.add(role.getId());
            }
            if (role.getName() !=null){
                sql.append(" and name = ?");
                jdbcParams.add(role.getName());
            }



            if (limit != null && limit.length > 0) {
                if (limit.length == 1) {
                    sql.append(" limit ?");
                    jdbcParams.add(limit[0]);
                } else if (limit.length == 2) {
                    sql.append(" limit ?,?");
                    jdbcParams.add(limit[0]);
                    jdbcParams.add(limit[1]);
                } else {
                    throw new RuntimeException("你的参数传多了，limit最大只能2个");
                }
            }
            Connection connection = DbUtils.getConnection();
            List<Role> result;
            try {
                result = qr.query(connection, sql.toString(), new BeanListHandler<Role>(Role.class), jdbcParams.toArray());
            } finally {
                connection.close();
            }
            return result;
        }

        @Override
        public int add(Role role) throws SQLException {
            String sql = "insert into role(id,name,age) values(?,?,?)";
            BigInteger id = null;
            Connection connection = DbUtils.getConnection();
            try {
                id = qr.insert(connection, sql, new ScalarHandler<>(), new Object[]{
                        role.getId(),
                        role.getName(),
                        role.getAge(),
                });
            } finally {
                connection.close();
            }
            if (id != null) {
                role.setId(id.intValue());
            }

            return 1;
        }

        @Override
        public int update(Role role) throws SQLException {
            String sql = "update role set name =?,age=? where id = ?";
            int rows;
            Connection connection = DbUtils.getConnection();
            try {
                rows = qr.update(connection, sql, new Object[]{
                        role.getName(),
                        role.getAge(),
                        role.getId()
                });
            } finally {
                connection.close();
            }
            return rows;
        }

        @Override
        public int delete(Integer id) throws SQLException {
            String sql = "delete from role where id = ?";
            int rows;
            Connection connection = DbUtils.getConnection();
            try {
                rows = qr.execute(connection, sql, new Object[]{id});
            } finally {
                connection.close();
            }
            return rows;
        }


        @Override
        public int count(Role role) throws SQLException {
            StringBuffer sql = new StringBuffer("select count(*) from role where 1=1");
            List<Object> jdbcParams = new LinkedList<>();
            if (role.getId() != null) {
                sql.append(" and id = ?");
                jdbcParams.add(role.getId());
            }
            if (role.getName() != null) {
                sql.append(" and name like ?");
                jdbcParams.add("%" + role.getName() + "%");
            }

            Connection connection = DbUtils.getConnection();
            Long result;
            try {
                result = qr.query(connection, sql.toString(), new ScalarHandler<Long>(), jdbcParams.toArray());
            } finally {
                connection.close();
            }
            if (result != null) {
                return result.intValue();
            }
            return 0;
        }

        @Override
        public Page searchPage(Role role, Integer page, Integer rows) throws SQLException {
            int total = this.count(role);
            List<Role> menuList = this.search(role, (page - 1) * rows, rows);
            return new Page(total,menuList);
        }
    }

